﻿using GenericAndReflection.IDAL;
using GenericAndReflection.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace GenericAndReflection.DAL
{
    /// <summary>
    /// 通用方法
    /// </summary>
    public class BaseDAL:IBaseDAL
    {
        private static string ConnectString = ConfigurationManager.ConnectionStrings["HOMEWORK"].ConnectionString;
        public BaseDAL()
        {
            
        }

        #region Private Method
        private T Trans<T>(Type type, SqlDataReader render)
        {
            object obj = Activator.CreateInstance(type);
            foreach (var prop in type.GetProperties())
            {
                //prop.SetValue(obj, render[prop.Name]);
                prop.SetValue(obj, render[prop.Name] is DBNull ? null : render[prop.Name]);
            }
            return (T)obj;
        }
        #endregion

        //查询所有记录
        public IEnumerable<T> FindAll<T>() where T:BaseModel
        {
            List<T> result = new List<T>();
            string sql = $"SELECT {string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.Name}]"))} FROM [{typeof(T).Name}]";
            Type type = typeof(T);
            
            using (SqlConnection conn = new SqlConnection(ConnectString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                conn.Open();

                var render = sqlCommand.ExecuteReader();
                while (render.Read())
                {
                    //object obj = Activator.CreateInstance(type);
                    //foreach (var prop in type.GetProperties())
                    //{
                    //    //prop.SetValue(obj, render[prop.Name]);
                    //    prop.SetValue(obj, render[prop.Name] is DBNull ? null:render[prop.Name]);
                    //}
                    
                    result.Add(Trans<T>(type, render));
                }
                return result;
            };
            
        }

        /// <summary>
        /// 获取单一实体的方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T FindOne<T>(int id) where T : BaseModel
        {
            string sql = $"SELECT {string.Join(",", typeof(T).GetProperties().Select(p=>$"[{p.Name}]"))} FROM [{typeof(T).Name}] WHERE ID={id}";
            Type type = typeof(T);
            object obj = Activator.CreateInstance(type);
            using (SqlConnection conn = new SqlConnection(ConnectString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql,conn);
                conn.Open();

                var render = sqlCommand.ExecuteReader();
                if (render.Read())
                {
                    return Trans<T>(type, render);
                }
                else 
                {  //ID 不存在，未找到记录
                    return null;
                }
            };
        }

        public T Add<T>(T t) where T : BaseModel
        {
            Type type = typeof(T);
            var tableName = type.Name;
            // 排除ID
            var propNames = string.Join(",", type.GetProperties(BindingFlags.Public|BindingFlags.Instance|BindingFlags.DeclaredOnly).Select(p=>$"{p.Name}") );
            foreach (var prop in type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly))
            {
                Console.WriteLine(prop.Name);
                Console.WriteLine(prop.GetValue(t));
            }
            var values = string.Join(",", type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Select(p => $"'{p.GetValue(t)??DBNull.Value}'"));
            string sql = $"Insert into [{tableName}] ({propNames}) values({values}) select @@IDENTITY";
            Console.WriteLine(sql);
            using (SqlConnection conn = new SqlConnection(ConnectString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                conn.Open();

                var render = sqlCommand.ExecuteNonQuery();
                if (render > 0) {
                    t.Id = render;
                    return t;
                }
                else
                {
                    return null;
                }
                
                    
            };

            throw new NotImplementedException();
        }

        public T Update<T>(T t) where T : BaseModel
        {
            throw new NotImplementedException();
        }

        public T Delete<T>(int id) where T : BaseModel
        {
            throw new NotImplementedException();
        }
    }
}
